const { getDbPool } = require('../infra/dbClient');
const db = getDbPool();
const { getLogger } = require('../logger');
const logger = getLogger('models.menuModel');
const { getMenusTree,sortTree, isSuperAdminById } = require("../utils/utils");
const  CodeService  = require('../services/codeService');

class MenuModel {
    // 获取所有菜单
    static async getAllMenus(page = 1, limit = 10, filters = {}, currentUserId = null) {
        const offset = (page - 1) * limit;
        let whereClause = 'WHERE m.is_delete = 0';
        let params = [];
        const selectFields = `
            m.id,
            m.name,
            m.module_code,
            m.access_type,
            m.is_show,
            m.code,
            m.node_type,
            m.icon,
            m.is_system,
            m.component_path,
            m.route_path,
            m.parent_id,
            m.path,
            m.status,
            m.sort,
            m.created_at,
            m.updated_at,
            p.name as parent_name,
            p.module_code as parent_code
        `;

        if (filters.name) { whereClause += ' AND m.name LIKE ?'; params.push(`%${filters.name}%`); }
        if (filters.module_code) { whereClause += ' AND m.module_code LIKE ?'; params.push(`%${filters.module_code}%`); }
        if (filters.code) { whereClause += ' AND m.code LIKE ?'; params.push(`%${filters.code}%`); }
        if (filters.node_type !== undefined) { whereClause += ' AND m.node_type = ?'; params.push(filters.node_type); }
        if (filters.parent_id !== undefined) { whereClause += ' AND m.parent_id = ?'; params.push(filters.parent_id); }
        if (filters.status !== undefined) { whereClause += ' AND m.status = ?'; params.push(filters.status); }


        try {
            let isAdmin = false;
            if (currentUserId) {
                isAdmin = await isSuperAdminById(currentUserId);
            }

            let baseSql;
            let baseParams;
            if (isAdmin) {
                baseSql = `
                    SELECT ${selectFields}
                    FROM menu m
                    LEFT JOIN menu p ON m.parent_id = p.id AND p.is_delete = 0
                    ${whereClause}
                    GROUP BY m.id
                    ORDER BY m.parent_id ASC, m.id ASC
                    LIMIT ${Number(limit)} OFFSET ${Number(offset)}
                `;
                baseParams = params;
            } else {
                baseSql = `
                    SELECT ${selectFields}
                    FROM menu m
                    LEFT JOIN menu p ON m.parent_id = p.id AND p.is_delete = 0
                    LEFT JOIN role_menu rm ON m.id = rm.menu_id
                    LEFT JOIN user_role ur ON rm.role_id = ur.role_id AND ur.user_id = ?
                    ${whereClause}
                    AND (m.access_type = 0 OR (m.access_type = 1 AND ur.user_id = ?))
                    GROUP BY m.id
                    ORDER BY m.parent_id ASC, m.id ASC
                    LIMIT ${Number(limit)} OFFSET ${Number(offset)}
                `;
                baseParams = [currentUserId, currentUserId, ...params];
            }

            const [rows] = await db.execute(baseSql, baseParams);

            // 如果不是超级管理员，则获取所有父菜单
            if (!isAdmin && rows.length > 0) {
                const parentIds = new Set();
                rows.forEach(menu => {
                    if (menu.path) {
                        const pathIds = menu.path.split(',').filter(id => id.trim());
                        pathIds.forEach(id => parentIds.add(parseInt(id)));
                    }
                    if (menu.parent_id) parentIds.add(menu.parent_id);
                });

                if (parentIds.size > 0) {
                    const parentSql = `
                        SELECT ${selectFields}
                        FROM menu m
                        LEFT JOIN menu p ON m.parent_id = p.id AND p.is_delete = 0
                        WHERE m.is_delete = 0 AND m.id IN (${Array(parentIds.size).fill('?').join(',')})
                    `;
                    const [parentRows] = await db.execute(parentSql, Array.from(parentIds));
                    const map = new Map();
                    rows.forEach(m => map.set(m.id, m));
                    parentRows.forEach(m => { if (!map.has(m.id)) map.set(m.id, m); });
                    return Array.from(map.values());
                }
            }

            return rows;
        } catch (error) {
            logger.error('获取所有菜单失败', { error });
            throw error;
        }
    }

    // 获取菜单总数
    static async getMenuCount(filters = {}, currentUserId = null) {
        let whereClause = 'WHERE m.is_delete = 0';
        let params = [];
        let fromClause = 'FROM menu m';

        // 构建过滤条件
        if (filters.name) {
            whereClause += ' AND m.name LIKE ?';
            params.push(`%${filters.name}%`);
        }
        if (filters.module_code) {
            whereClause += ' AND m.module_code LIKE ?';
            params.push(`%${filters.module_code}%`);
        }
        if (filters.code) {
            whereClause += ' AND m.code LIKE ?';
            params.push(`%${filters.code}%`);
        }
        if (filters.node_type !== undefined) {
            whereClause += ' AND m.node_type = ?';
            params.push(filters.node_type);
        }
        if (filters.parent_id !== undefined) {
            whereClause += ' AND m.parent_id = ?';
            params.push(filters.parent_id);
        }
        if (filters.status !== undefined) {
            whereClause += ' AND m.status = ?';
            params.push(filters.status);
        }

        // 如果提供了用户ID，则根据用户权限过滤菜单数量
        if (currentUserId) {
            // 检查用户是否为超级管理员
            const adminRoleSql = `
                SELECT r.id FROM role r
                INNER JOIN user_role ur ON r.id = ur.role_id
                WHERE ur.user_id = ? AND (r.code = 'SUPER_ADMIN' OR (r.is_system = 1 AND r.is_protected = 1)) AND r.is_delete = 0
            `;
            const [adminRole] = await db.execute(adminRoleSql, [currentUserId]);
            
            // 非超级管理员需要根据access_type和角色权限过滤菜单数量
            if (adminRole.length === 0) {
                // 权限过滤逻辑：
                // 1. access_type为0的菜单默认允许访问
                // 2. access_type为1的菜单需要角色绑定
                fromClause += `
                    LEFT JOIN role_menu rm ON m.id = rm.menu_id
                    LEFT JOIN user_role ur ON rm.role_id = ur.role_id AND ur.user_id = ?`;
                whereClause += ' AND (m.access_type = 0 OR (m.access_type = 1 AND ur.user_id = ?))';
                params.push(currentUserId, currentUserId);
            }
        }

        const sql = `SELECT COUNT(*) as total ${fromClause} ${whereClause} GROUP BY m.id`;

        try {
            const [rows] = await db.execute(sql, params);
            return rows.length;
        } catch (error) {
            logger.error('获取菜单总数失败', { error });
            throw error;
        }
    }

    // 根据ID获取菜单
    static async findById(id) {
        const sql = `
            SELECT 
                m.id,
                m.name,
                m.module_code,
                m.access_type,
                m.is_show,
                m.code,
                m.node_type,
                m.icon,
                m.component_path,
                m.route_path,
                m.parent_id,
                m.path,
                m.status,
                m.created_at,
                m.updated_at,
                p.name as parent_name,
                p.module_code as parent_code
            FROM menu m
            LEFT JOIN menu p ON m.parent_id = p.id AND p.is_delete = 0
            WHERE m.id = ? AND m.is_delete = 0
        `;

        
        try {
            const [rows] = await db.execute(sql, [id]);
            return rows[0] || [];
        } catch (error) {
            logger.error('根据ID获取菜单失败', { error });
            throw error;
        }
    }

    // 根据编码获取菜单
    static async findByCode(code) {
        const sql = `
            SELECT 
                m.id,
                m.name,
                m.module_code,
                m.access_type,
                m.is_show,
                m.code,
                m.node_type,
                m.icon,
                m.component_path,
                m.route_path,
                m.parent_id,
                m.path,
                m.status,
                m.created_at,
                m.updated_at
            FROM menu m
            WHERE m.module_code = ? AND m.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [code]);
            return rows[0] || null;
        } catch (error) {
            logger.error('根据编码获取菜单失败', { error });
            throw error;
        }
    }

    // 根据名称获取菜单
    static async findByName(name) {
        const sql = `
            SELECT 
                m.id,
                m.name,
                m.module_code,
                m.access_type,
                m.is_show,
                m.code,
                m.node_type,
                m.icon,
                m.component_path,
                m.route_path,
                m.parent_id,
                m.path,
                m.status,
                m.created_at,
                m.updated_at
            FROM menu m
            WHERE m.name = ? AND m.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [name]);
            return rows[0] || null;
        } catch (error) {
            logger.error('根据名称获取菜单失败', { error });
            throw error;
        }
    }

    // 根据父级ID获取子菜单
    static async findByParentId(parentId) {
        const sql = `
            SELECT 
                m.id,
                m.name,
                m.module_code,
                m.access_type,
                m.is_show,
                m.code,
                m.node_type,
                m.icon,
                m.component_path,
                m.route_path,
                m.parent_id,
                m.path,
                m.status,
                m.created_at,
                m.updated_at
            FROM menu m
            WHERE m.parent_id = ? AND m.is_delete = 0
            ORDER BY m.id ASC
        `;

        try {
            const [rows] = await db.execute(sql, [parentId]);
            return rows;
        } catch (error) {
            logger.error('根据父级ID获取子菜单失败', { error });
            throw error;
        }
    }

    // 检查菜单编码是否存在
    static async isCodeExists(code, excludeId = null) {
        try {
            let sql = 'SELECT COUNT(*) as count FROM menu WHERE code = ? AND is_delete = 0';
            let params = [code];

            if (excludeId) {
                sql += ' AND id != ?';
                params.push(excludeId);
            }

            const [rows] = await db.execute(sql, params);
            return rows[0].count > 0;
        } catch (error) {
            logger.error('检查模块编码是否存在失败', { error, code, excludeId });
            throw error;
        }
    }


    // 检查模块编码是否已存在
    static async isModuleCodeExists(moduleCode, excludeId = null) {
        try {
            let sql = 'SELECT COUNT(*) as count FROM menu WHERE module_code = ? AND is_delete = 0';
            let params = [moduleCode];

            if (excludeId) {
                sql += ' AND id != ?';
                params.push(excludeId);
            }

            const [rows] = await db.execute(sql, params);
            return rows[0].count > 0;
        } catch (error) {
            logger.error('检查模块编码是否存在失败', { error, moduleCode, excludeId });
            throw error;
        }
    }
    
    // 检查权限编码是否存在（兼容旧代码，内部调用CodeService）
    static async isPermissionCodeExists(permissionCode, excludeId = null) {
        try {
            // 如果提供了excludeId，我们需要特殊处理，先使用CodeService检查
            let exists = await CodeService.checkCodeExists('MENU_CODE', permissionCode);
            
            // 如果CodeService发现存在，但提供了excludeId，我们需要进一步检查是否是要排除的记录
            if (exists && excludeId) {
                const sql = 'SELECT id FROM menu WHERE code = ? AND id = ? AND is_delete = 0';
                const [rows] = await db.execute(sql, [permissionCode, excludeId]);
                // 如果找到的是要排除的记录，则返回false
                if (rows.length > 0) {
                    return false;
                }
            }
            
            return exists;
        } catch (error) {
            logger.error('检查权限编码是否存在失败', { error, permissionCode, excludeId });
            throw error;
        }
    }

    // 检查菜单名称是否存在
    static async isNameExists(name, excludeId = null) {
        let sql = 'SELECT COUNT(*) as count FROM menu WHERE name = ? AND is_delete = 0';
        let params = [name];

        if (excludeId) {
            sql += ' AND id != ?';
            params.push(excludeId);
        }

        try {
            const [rows] = await db.execute(sql, params);
            return rows[0].count > 0;
        } catch (error) {
            logger.error('检查菜单名称是否存在失败', { error });
            throw error;
        }
    }

    // 获取同级菜单的最大排序值
    static async getMaxSort(parentId = null) {
        const sql = `
            SELECT COALESCE(MAX(sort), 0) as maxSort 
            FROM menu 
            WHERE parent_id = ? AND is_delete = 0
        `;
        
        try {
            const [rows] = await db.execute(sql, [parentId]);
            return rows[0].maxSort;
        } catch (error) {
            logger.error('获取同级菜单最大排序值失败', { error, parentId });
            throw error;
        }
    }

    // 计算菜单路径
    static async calculatePath(parentId) {
        if (!parentId) {
            return '';
        }
        
        try {
            const parentMenu = await this.findById(parentId);
            if (parentMenu) {
                return parentMenu.path ? `${parentMenu.path},${parentId}` : parentId.toString();
            }
            return '';
        } catch (error) {
            logger.error('计算菜单路径失败', { parentId, error });
            throw error;
        }
    }

    // 创建菜单
    static async create(menuData) {
        let { name, module_code, code=null, access_type, is_show, node_type, icon='', component_path='', route_path='', parent_id=null } = menuData;
        
        // 获取同级菜单的最大排序值，新菜单的排序为最大值+1
        const maxSort = await this.getMaxSort(parent_id);
        const newSort = maxSort + 1;
        
        // 如果没有提供code，则自动生成角色编码（使用generateUniqueCode确保编码唯一）
        if (!code) {
            try {
                code = await CodeService.generateUniqueCode('MENU_CODE');
                logger.info('自动生成菜单编码', { code, menuName: name });
            } catch (error) {
                logger.error('生成菜单编码失败', { error, menuName: name });
                throw new Error('生成菜单编码失败：' + error.message);
            }
        } else {
            // 如果提供了code，则检查编码是否已存在
            const codeExists = await CodeService.checkCodeExists('MENU_CODE', code);
            if (codeExists) {
                logger.error('菜单编码已存在', { code, menuName: name });
                throw new Error(`菜单编码 ${code} 已存在，请更换其他编码`);
            }
        }
        // 先插入基本数据，path暂时为空，包含sort字段
        const sql = `
            INSERT INTO menu (code,name, module_code, access_type, is_show, node_type, icon, component_path, route_path, parent_id, path, sort)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;

        let connection = null;
        try {
            // 开始事务
            connection = await db.getConnection();
            await connection.beginTransaction();
            
            // 插入数据，包含计算出的排序值
            const [result] = await connection.execute(sql, [code, name, module_code, access_type, is_show, node_type, icon, component_path, route_path, parent_id, '', newSort]);
            const insertId = result.insertId;
            
            // 计算完整path（存储从根节点到当前节点的路径）
            let fullPath = '';
            if (parent_id) {
                const parentPath = await this.calculatePath(parent_id);
                fullPath = parentPath ? `${parentPath},${insertId}` : insertId.toString();
            } else {
                fullPath = ''; // 根节点path为空
            }
            
            // 更新path字段
            const updatePathSql = `UPDATE menu SET path = ? WHERE id = ?`;
            await connection.execute(updatePathSql, [fullPath, insertId]);
            
            await connection.commit();
            return insertId;
        } catch (error) {
            if (connection) {
                await connection.rollback();
            }
            logger.error('创建菜单失败', { error });
            throw error;
        } finally {
            if (connection) {
                connection.release();
            }
        }
    }

    // 更新菜单
    static async update(id, updateData) {
        const { name, module_code, code, node_type, icon, component_path, route_path, parent_id, status } = updateData;
        
        // 开始事务
        const connection = await db.getConnection();
        try {
            await connection.beginTransaction();
            
            // 如果更新了parent_id，需要重新计算path
            let path = '';
            if (parent_id !== undefined) {
                // 计算当前菜单的新路径：父路径 + 当前菜单ID
                const parentPath = await this.calculatePath(parent_id);
                path = parentPath ? `${parentPath},${id}` : id.toString();
            } else {
                // 如果没有更新parent_id，保留原path
                const currentMenu = await this.findById(id);
                path = currentMenu.path;
            }

            const sql = `
                UPDATE menu 
                SET name = ?, module_code = ?, code = ?, node_type = ?, icon = ?, component_path = ?, 
                    route_path = ?, parent_id = ?, path = ?, status = ?, updated_at = NOW()
                WHERE id = ? AND is_delete = 0
            `;

            const [result] = await connection.execute(sql, [name, module_code, code, node_type, icon, component_path, route_path, parent_id, path, status, id]);
            
            // 如果更新了parent_id，需要更新所有子菜单的path
            if (parent_id !== undefined) {
                // 先获取更新后的完整路径
                const updatedMenu = await this.findById(id);
                await this.updateChildrenPath(id, updatedMenu.path, connection);
            }
            
            await connection.commit();
            return result.affectedRows;
        } catch (error) {
            await connection.rollback();
            logger.error('更新菜单失败', { error });
            throw error;
        } finally {
            connection.release();
        }
    }

    // 更新菜单排序
    static async updateSort(id, newSort) {
        const sql = `
            UPDATE menu 
            SET sort = ?, updated_at = NOW()
            WHERE id = ? AND is_delete = 0
        `;

        try {
            const [result] = await db.execute(sql, [newSort, id]);
            return result.affectedRows;
        } catch (error) {
            logger.error('更新菜单排序失败', { error, id, newSort });
            throw error;
        }
    }

    // 批量更新菜单排序
    static async batchUpdateSort(menuSorts) {
        const connection = await db.getConnection();
        try {
            await connection.beginTransaction();
            
            for (const { id, sort } of menuSorts) {
                const sql = `
                    UPDATE menu 
                    SET sort = ?, updated_at = NOW()
                    WHERE id = ? AND is_delete = 0
                `;
                await connection.execute(sql, [sort, id]);
            }
            
            await connection.commit();
            return menuSorts.length;
        } catch (error) {
            await connection.rollback();
            logger.error('批量更新菜单排序失败', { error, menuSorts });
            throw error;
        } finally {
            connection.release();
        }
    }

    // 更新子菜单的path
    static async updateChildrenPath(parentId, parentPath, connection = null) {
        // 使用传入的连接或默认连接
        const dbConn = connection || db;
        
        try {
            // 获取所有直接子菜单
            const childrenSql = `SELECT id FROM menu WHERE parent_id = ? AND is_delete = 0`;
            const [children] = await dbConn.execute(childrenSql, [parentId]);
            
            // 为每个子菜单计算新路径并更新
            for (const child of children) {
                // 子菜单的新路径应该是父路径 + 父ID + 子ID
                const childNewPath = parentPath ? `${parentPath},${child.id}` : child.id.toString();
                
                // 更新子菜单路径
                const updateSql = `UPDATE menu SET path = ? WHERE id = ? AND is_delete = 0`;
                await dbConn.execute(updateSql, [childNewPath, child.id]);
                
                // 递归更新子菜单的子菜单
                await this.updateChildrenPath(child.id, childNewPath, dbConn);
            }
        } catch (error) {
            logger.error('更新子菜单path失败', { parentId, error });
            throw error;
        }
    }

    // 删除菜单（软删除）
    static async deleteById(id) {
        const sql = `
            UPDATE menu 
            SET is_delete = 1, updated_at = NOW()
            WHERE id = ? AND is_delete = 0
        `;

        try {
            const [result] = await db.execute(sql, [id]);
            return result.affectedRows;
        } catch (error) {
            logger.error('删除菜单失败', { error });
            throw error;
        }
    }

    // 检查菜单是否可以删除
    static async isDeletable(id) {
        const sql = `
            SELECT 
                COUNT(child.id) as children_count,
                COUNT(rm.id) as role_bind_count
            FROM menu m
            LEFT JOIN menu child ON m.id = child.parent_id AND child.is_delete = 0
            LEFT JOIN role_menu rm ON m.id = rm.menu_id
            WHERE m.id = ? AND m.is_delete = 0
            GROUP BY m.id
        `;

        try {
            const [rows] = await db.execute(sql, [id]);
            if (rows.length === 0) return false;
            
            const menu = rows[0];
            return menu.children_count === 0 && menu.role_bind_count === 0;
        } catch (error) {
            logger.error('检查菜单是否可删除失败', { error });
            throw error;
        }
    }

    // 获取菜单树结构
    static async getMenuTree(currentUserId = null) {
        const menus = await this.getAllMenus(1, 1000,{}, currentUserId ); // 根据用户权限获取菜单

        const treemenus = getMenusTree(menus);

        // const sortmenutree = sortTree(treemenus);

        return treemenus;
    }

    // 构建树结构
    static buildTree(menus, parentId = null) {
        const tree = [];
        
        for (const menu of menus) {
            if (menu.parent_id === parentId) {
                const children = this.buildTree(menus, menu.id);
                if (children.length > 0) {
                    menu.children = children;
                }
                tree.push(menu);
            }
        }

        // 排序
        const sortmenutree = sortTree(tree);

        return sortmenutree;
    }

    // 获取菜单的所有子菜单（包括子子菜单）
    static async getAllChildren(id) {
        const sql = `
            SELECT 
                m.id,
                m.name,
                m.module_code,
                m.access_type,
                m.is_show,
                m.code,
                m.node_type,
                m.icon,
                m.component_path,
                m.route_path,
                m.parent_id,
                m.path,
                m.status,
                m.created_at,
                m.updated_at
            FROM menu m
            WHERE m.path LIKE CONCAT('%', ?, '%') AND m.id != ? AND m.is_delete = 0
            ORDER BY m.path ASC
        `;

        try {
            const [rows] = await db.execute(sql, [id, id]);
            return rows;
        } catch (error) {
            logger.error('获取所有子菜单失败', { error });
            throw error;
        }
    }

    // 获取菜单的所有父菜单
    static async getAllParents(id) {
        const menu = await this.findById(id);
        if (!menu || !menu.path) {
            return [];
        }

        const parentIds = menu.path.split(',').map(id => parseInt(id.trim()));
        const sql = `
            SELECT 
                m.id,
                m.name,
                m.module_code,
                m.access_type,
                m.is_show,
                m.code,
                m.node_type,
                m.icon,
                m.component_path,
                m.route_path,
                m.parent_id,
                m.path,
                m.status,
                m.created_at,
                m.updated_at
            FROM menu m
            WHERE m.id IN (${parentIds.map(() => '?').join(',')}) AND m.is_delete = 0
            ORDER BY FIELD(m.id, ${parentIds.map(() => '?').join(',')})
        `;

        try {
            const [rows] = await db.execute(sql, [...parentIds, ...parentIds]);
            return rows;
        } catch (error) {
            logger.error('获取所有父菜单失败', { error });
            throw error;
        }
    }


    // 获取角色绑定的菜单
    static async getBindMenus(roleId,currentUserId = null,isMinfield = false) {
        try {
            const selectFields = isMinfield ? `
                SELECT 
                    m.id,
                    m.name,
                    m.parent_id,
                    m.node_type,
                    m.icon,
                    m.is_show,
                    m.component_path,
                    m.route_path,
                    m.sort,
                    m.path
            ` : `
                SELECT 
                    m.id,
                    m.name,
                    m.module_code,
                    m.access_type,
                    m.is_show,
                    m.code,
                    m.node_type,
                    m.icon,
                    m.component_path,
                    m.route_path,
                    m.parent_id,
                    m.path,
                    m.status,
                    m.sort,
                    m.level,
                    m.icon_url,
                    m.create_by,
                    m.update_by,
                    m.created_at,
                    m.updated_at,
                    m.is_system,
                    m.is_delete
            `;

            const [roleRows] = await db.execute(
                'SELECT code, is_system, is_protected FROM role WHERE id = ? AND is_delete = 0',
                [roleId]
            );
            const isSuperRole = roleRows.length > 0 && (roleRows[0].code === 'SUPER_ADMIN' || (roleRows[0].is_system === 1 && roleRows[0].is_protected === 1));

            let sql, params;
            if (isSuperRole) {
                sql = `
                    ${selectFields}
                    FROM menu m
                    WHERE m.is_delete = 0
                    ORDER BY m.parent_id ASC, m.id ASC
                `;
                params = [];
            } else {
                sql = `
                    ${selectFields}
                    FROM menu m
                    LEFT JOIN role_menu rm ON m.id = rm.menu_id AND rm.role_id = ?
                    WHERE m.is_delete = 0
                    AND (m.access_type = 0 OR (m.access_type = 1 AND rm.role_id IS NOT NULL))
                    ORDER BY m.parent_id ASC, m.id ASC
                `;
                params = [roleId];
            }

            const [bindMenus] = await db.execute(sql, params);

            // 收集所有需要包含的父级ID
            const parentIds = new Set();
            bindMenus.forEach(menu => {
                if (menu.path) {
                    const pathIds = menu.path.split(',').filter(id => id.trim());
                    pathIds.forEach(id => parentIds.add(parseInt(id)));
                }
                if (menu.parent_id) {
                    parentIds.add(menu.parent_id);
                }
            });

            let allMenus = bindMenus;
            if (parentIds.size > 0) {
                const parentSql = `
                    ${selectFields}
                    FROM menu m
                    WHERE m.is_delete = 0 AND m.id IN (${Array(parentIds.size).fill('?').join(',')})
                    ORDER BY m.parent_id ASC, m.id ASC
                `;
                const [parentRows] = await db.execute(parentSql, Array.from(parentIds));
                const menuMap = new Map();
                bindMenus.forEach(menu => menuMap.set(menu.id, menu));
                parentRows.forEach(menu => { if (!menuMap.has(menu.id)) menuMap.set(menu.id, menu); });
                allMenus = Array.from(menuMap.values());
            }

            return allMenus;
        } catch (error) {
            logger.error('获取角色已绑定菜单失败', { error, roleId, isMinfield });
            throw error;
        }
    }

    // 根据用户ID获取菜单
    // 支持access_type权限过滤：0-公共菜单(默认访问)，1-私有菜单(需要角色绑定)
    static async findMenusByUserId(userId, isMinfield = false) {
        try {
            // 使用全局方法检查是否是超级管理员
            const isSuperAdmin = await isSuperAdminById(userId);
            
            // 根据isMinfield参数选择查询字段
            const selectFields = isMinfield ? `
                SELECT 
                    m.id,
                    m.name,
                    m.parent_id,
                    m.node_type,
                    m.icon,
                    m.is_show,
                    m.component_path,
                    m.route_path,
                    m.sort
            ` : `
                SELECT 
                    m.id,
                    m.name,
                    m.parent_id,
                    m.node_type,
                    m.icon,
                    m.is_show,
                    m.component_path,
                    m.route_path,
                    m.sort,
                    m.level,
                    m.icon_url,
                    m.create_by,
                    m.created_at,
                    m.update_by,
                    m.updated_at
            `;

            let sql, params, allMenus = [];
            
            if (isSuperAdmin) {
                // 超级管理员可以查看所有菜单，无需额外处理父级菜单
                sql = `
                    ${selectFields}
                    FROM menu m
                    WHERE m.is_delete = 0
                    ${isMinfield ? 'AND m.status = 0' : ''}
                    ORDER BY m.parent_id ASC, m.id ASC
                `;
                params = [];
                
                const [menus] = await db.execute(sql, params);
                allMenus = menus;
            } else {
                // 普通用户根据access_type、角色和权限过滤菜单
                // 1. access_type为0的菜单默认允许访问
                // 2. access_type为1的菜单需要角色绑定
                sql = `
                    ${selectFields}
                    FROM menu m
                    LEFT JOIN role_menu rm ON m.id = rm.menu_id
                    LEFT JOIN user_role ur ON rm.role_id = ur.role_id AND ur.user_id = ?
                    WHERE m.is_delete = 0
                    AND (m.access_type = 0 OR (m.access_type = 1 AND ur.user_id = ?))
                    ${isMinfield ? 'AND m.status = 0' : ''}
                    ORDER BY m.parent_id ASC, m.id ASC
                `;
                params = [userId, userId];
                
                const [bindMenus] = await db.execute(sql, params);

                // 收集所有需要包含的父级ID
                const parentIds = new Set();
                bindMenus.forEach(menu => {
                    if (menu.parent_id) {
                        // 解析path字段获取所有父级ID
                        if (menu.path) {
                            const pathIds = menu.path.split(',').filter(id => id.trim());
                            pathIds.forEach(id => parentIds.add(parseInt(id)));
                        }
                        if (menu.parent_id) {
                            parentIds.add(menu.parent_id);
                        }
                    }
                });

                // 获取所有父级菜单
                let parentMenus = [];
                if (parentIds.size > 0) {
                    const parentSql = `
                        ${selectFields}
                        FROM menu m
                        WHERE m.is_delete = 0 AND m.id IN (${Array(parentIds.size).fill('?').join(',')})
                        ${isMinfield ? 'AND m.status = 0' : ''}
                        ORDER BY m.parent_id ASC, m.id ASC
                    `;
                    const [parentRows] = await db.execute(parentSql, Array.from(parentIds));
                    parentMenus = parentRows;
                }

                // 合并绑定菜单和父级菜单，并去重
                const menuMap = new Map();
                
                // 先添加绑定菜单
                bindMenus.forEach(menu => {
                    menuMap.set(menu.id, menu);
                });
                
                // 再添加父级菜单（如果不存在的话）
                parentMenus.forEach(menu => {
                    if (!menuMap.has(menu.id)) {
                        menuMap.set(menu.id, menu);
                    }
                });
                
                allMenus = Array.from(menuMap.values());
            }

            return allMenus;

        } catch (error) {
            logger.error('根据用户ID获取菜单失败', { error, userId, isMinfield });
            throw error;
        }
    }

    // 新增获取用户权限码方法
    static async getUserPermissionCodes(userId) {
        // 检查是否是超级管理员
        const adminRoleSql = `
            SELECT r.id FROM role r
            INNER JOIN user_role ur ON r.id = ur.role_id
            WHERE ur.user_id = ? AND (r.code = 'SUPER_ADMIN' OR (r.is_system = 1 AND r.is_protected = 1)) AND r.is_delete = 0
        `;

        try {
            const [adminRole] = await db.execute(adminRoleSql, [userId]);
            if (adminRole.length > 0) {
                // 超级管理员拥有所有权限
                const [permissions] = await db.execute('SELECT code FROM menu WHERE code IS NOT NULL AND is_delete = 0');
                return permissions.map(p => p.code);
            }

            // 普通用户根据角色获取权限码
            const query = `
                SELECT DISTINCT m.code 
                FROM menu m
                JOIN role_menu rm ON m.id = rm.menu_id
                JOIN user_role ur ON rm.role_id = ur.role_id
                WHERE ur.user_id = ? AND m.code IS NOT NULL AND m.is_delete = 0
            `;
            
            const [results] = await db.execute(query, [userId]);
            return results.map(r => r.code);
        } catch (error) {
            logger.error('获取用户权限码失败', { error });
            throw error;
        }
    }

    // 获取菜单统计信息
    static async getMenuStats() {
        const sql = `
            SELECT 
                COUNT(*) as total_menus,
                COUNT(CASE WHEN node_type = 1 THEN 1 END) as directory_menus,
                COUNT(CASE WHEN node_type = 2 THEN 1 END) as menu_menus,
                COUNT(CASE WHEN node_type = 3 THEN 1 END) as button_menus,
                COUNT(CASE WHEN parent_id IS NULL THEN 1 END) as root_menus,
                COUNT(CASE WHEN parent_id IS NOT NULL THEN 1 END) as child_menus,
                COUNT(CASE WHEN status = 0 THEN 1 END) as active_menus,
                COUNT(CASE WHEN status = 1 THEN 1 END) as inactive_menus
            FROM menu 
            WHERE is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql);
            return rows[0];
        } catch (error) {
            logger.error('获取菜单统计信息失败', { error });
            throw error;
        }
    }
}

module.exports = MenuModel;